﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using ADA.Entity;

namespace ADA.Data
{
    public class DALUser
    {
        //private string conStr = ConfigurationManager.AppSettings["connectionString"];
        private string conStr = Common.CONNECTION_STRING;

        public DataTable getAllUser()
        {
            DataTable tblUserInfo = new DataTable();

            string sql = @"SELECT   [User].[ID],
                                    [User].[Name],
                                    [User].[Email],
                                    [User].[RegisterDate],
                                    [User].[ViolationNumber]
                            FROM    [User]";

            SqlConnection conn = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand(sql, conn);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(tblUserInfo);
            return tblUserInfo;
        }

        public DataTable getUserInfo(long userID)
        {
            DataTable tblUser = new DataTable();

            string sql = @"
                SELECT		[User].[Name],
                            [User].[Password],
			                [User].[ProfilePicture],
			                [User].[Email],
			                [User].[Phone],
			                [User].[Address],
                            [User].[ProvinceID],
			                [Province].[Name] AS [Province],
                            [User].[RegisterDate],
                            [User].[ViolationNumber],
                            [User].[Status]
                FROM		[User], [Province]
                WHERE		[Province].[ID] = [User].[ProvinceID]
	                AND		[User].[ID] = @userID";

            SqlConnection conn = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@userID", SqlDbType.BigInt).Value = userID;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(tblUser);
            return tblUser;
        }

        public void deleteUser(long userID)
        {
            string sql = @"DELETE FROM  [User]
                                WHERE   [User].[ID] = @userID";

            SqlConnection conn = new SqlConnection(conStr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@userID", SqlDbType.BigInt).Value = userID;

            cmd.ExecuteNonQuery();
            conn.Close();
        }
    }
}
